/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package tourmsystem.bookingmn.controller;

import java.sql.*;
import tourmsystem.tourmanaging.classes.Tour;

/**
 *
 * @author Thanh Nga
 */
public class SqlConnect {
      private String user;
     private String pwd;
     private String db;
     private String url;
     private String driver;
     private Connection conn;
    private PreparedStatement prstm;
    private ResultSet rs;
/**
 *
 * @author THANH NGA
 */
    public SqlConnect() {
        user = "root";
        pwd = "";
        db = "tourmanagement";
        url = "jdbc:mysql://localhost:3306/tourmanagement";
        driver = "com.mysql.jdbc.Driver";
        conn = null;
        prstm= null;
        rs= null;
    }

    public void connect() {
        try {
            Class.forName(driver).newInstance();
            conn = DriverManager.getConnection(url, user, pwd);
        } catch(Exception e) {
            System.out.println(" not ok" + e.getMessage());
        }
       }

    public ResultSet read(String query) {
       try {
               // connect();
                Statement sttm = conn.createStatement();
                return sttm.executeQuery(query);
            } catch(Exception e) {
               return null;
            }
        }
       public boolean tonTaiIdkh(String idkh){
        String sql = "SELECT* FROM customer WHERE ID = ?";
        try{
            prstm = (PreparedStatement) conn.prepareStatement(sql);
            prstm.setString(1, idkh);
            rs = prstm.executeQuery();
            if(rs.next()){
                return true;
            }
        }catch(Exception e){
            return false;
            }
        return false;
    }
    
    public void themKh(int ID, String Name, int Phone, String Email, String Address, String accountNum, int quantity){
        String d = "INSERT INTO customer VALUES(?,?,?,?,?,?,?)";
        try{
            prstm = (PreparedStatement) conn.prepareStatement(d);
            prstm.setInt(1, ID);
            prstm.setString(2, Name);
            prstm.setInt(3, Phone);
            prstm.setString(4, Email);
            prstm.setString(5, Address);
            prstm.setString(6, accountNum);
            prstm.setInt(7, quantity);
            prstm.executeUpdate();
        }catch(Exception e){}
    }
    public Tour tim(String mua){
        String sql = "SELECT* FROM tour WHERE season = ?";
        Tour  t = new Tour();
        try{
            prstm = (PreparedStatement) conn.prepareStatement(sql);
            prstm.setString(1, mua);
            rs = prstm.executeQuery();
            if(rs.next()){
                t.setId(rs.getString(1));
                t.setName(rs.getString(2));
                t.setStartDate(rs.getDate(3));
                t.setDuration(rs.getInt(4));
                t.setType(rs.getInt(5));
                t.setSchedule(rs.getString(7));
                t.setPrice(rs.getFloat(8));
                t.setDiscount(rs.getFloat(9));
                t.setMaxP(rs.getInt(10));
                t.setImgLink(rs.getString(11));
            }
        }catch(Exception e){}
        return t;
    }
    
  /*   public Tour timDong(String dong){
        String sql = "SELECT* FROM tour WHERE season = ?";
        Tour  t = new Tour();
        try{
            prstm = (PreparedStatement) conn.prepareStatement(sql);
            prstm.setString(1, dong);
            rs = prstm.executeQuery();
            if(rs.next()){
                t.setId(rs.getString(1));
                t.setName(rs.getString(2));
                t.setStartDate(rs.getDate(3));
                t.setDuration(rs.getInt(4));
                t.setType(rs.getInt(5));
                t.setState(rs.getInt(6));
                t.setSchedule(rs.getString(7));
                t.setPrice(rs.getFloat(8));
                t.setDiscount(rs.getFloat(9));
                t.setMaxPeople(rs.getInt(10));
                t.setImgLinks(rs.getString(11));
                t.setSeason(rs.getString(12));
            }
        }catch(Exception e){}
        return t;
    }
      public Tour timDong(String dong){
        String sql = "SELECT* FROM tour WHERE season = ?";
        Tour  t = new Tour();
        try{
            prstm = (PreparedStatement) conn.prepareStatement(sql);
            prstm.setString(1, dong);
            rs = prstm.executeQuery();
            if(rs.next()){
                t.setId(rs.getString(1));
                t.setName(rs.getString(2));
                t.setStartDate(rs.getDate(3));
                t.setDuration(rs.getInt(4));
                t.setType(rs.getInt(5));
                t.setState(rs.getInt(6));
                t.setSchedule(rs.getString(7));
                t.setPrice(rs.getFloat(8));
                t.setDiscount(rs.getFloat(9));
                t.setMaxPeople(rs.getInt(10));
                t.setImgLinks(rs.getString(11));
                t.setSeason(rs.getString(12));
            }
        }catch(Exception e){}
        return t;
    }*/
  /*  int rowcounttour;
    public Tour[] timTenGV(String tenGV){
        String sql = "SELECT* FROM giangVien WHERE hoTenGV = ?";
        try{
            prstm = (PreparedStatement) conn.prepareStatement(sql);
            prstm.setString(1, tenGV);
            rs = prstm.executeQuery();
            
            //lay so luong mang toi da
            rowcountGV = 0;
            if (rs.last()) {
            rowcountGV = rs.getRow();
                rs.beforeFirst(); 
            }
            
            giangVien[] gv = new giangVien[rowcountGV];
            for(int i=0; i<rowcountGV; i++)
                gv[i] = new giangVien();
            int index = 0;
            while(rs.next()){
                gv[index].setMaGV(rs.getString(1));
                gv[index].setHoTenGV(rs.getString(2));
                gv[index].setNgaySinhGV(rs.getString(3));
                gv[index].setDiaChiGV(rs.getString(4));
                gv[index].setGioiTinhGV(rs.getString(5));
                gv[index].setKhoaGV(rs.getString(6));
                index++;
            }
            return gv;
        }catch(Exception e){
            return null;
        }
    }
    
    public rowCount getInfoGV(){
        rowCount rc = new rowCount();
        rc.setInfo(rowcountGV);
        return rc;
    }
    
*/
    public boolean edit(String query) {
       try {
                connect();
                Statement sttm = conn.createStatement();
                sttm.execute(query);
                System.out.println("ok");
                return true;
            } catch(Exception e) {
               System.out.println("co lo " +e.getMessage());
                return false;
            }
         finally 
            {
                close();
            }
          }
    public void close() {
        if(conn != null) {
            try {
                conn.close();
            } catch(Exception e) {

            }
        }
    
    }
}
